﻿using YunxSoft.AfterSchoolClazz.Converters;
using YunxSoft.AfterSchoolClazz.Models;
using NPOI.DDF;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Diagnostics.Eventing.Reader;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YunxSoft.AfterSchoolClazz.Services
{
    public interface IScheduleService
    {
        void GenerateAttendanceSheet(List<Schedule> schedules, List<StudentSchedule> studentSchedules, ExportType exportType, string saveFilePath);
        List<Schedule> GetSchedules(string filePath, string sheetName);
        string ValidateCourse(List<Schedule> schedules, List<StudentSchedule> studentSchedules);
        List<StudentSchedule> GetStudentSchedules(string filePath, string[] sheetList);
    }

    public class ScheduleService : IScheduleService
    {

        public List<Schedule> GetSchedules(string filePath, string sheetName)
        {
            var schedules = new List<Schedule>();

            using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new XSSFWorkbook(fs);
                ISheet sheet = workbook.GetSheet(sheetName);

                int totalColumns = sheet.GetRow(0).LastCellNum;
                int daysOfWeek = (totalColumns + 1) / 6;  // 每个星期有5列数据，间隔1列

                for (int week = 0; week < daysOfWeek; week++)
                {
                    int startCol = week * 6;

                    for (int row = 1; row <= sheet.LastRowNum; row++)  // 假设第一行为表头
                    {
                        IRow currentRow = sheet.GetRow(row);

                        if (currentRow == null || currentRow.GetCell(startCol) == null || string.IsNullOrEmpty(currentRow.GetCell(startCol).ToString()))
                            continue;

                        var scheduleData = new Schedule
                        {
                            Date = sheet.GetRow(0).GetCell(startCol).ToString(),  // 获取日期列
                            Course = currentRow.GetCell(startCol).ToString(),
                            Location = currentRow.GetCell(startCol + 1).ToString(),
                            Teacher = currentRow.GetCell(startCol + 2).ToString(),
                            Grade = currentRow.GetCell(startCol + 3).ToString(),
                            NumberOfStudents = currentRow.GetCell(startCol + 4).ToString()
                        };

                        schedules.Add(scheduleData);
                    }
                }
            }

            return schedules;
        }
        public List<StudentSchedule> GetStudentSchedules(string filePath, string[] sheetList)
        {
            var studentSchedules = new List<StudentSchedule>();

            using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new XSSFWorkbook(fs);

                foreach (var sheetName in sheetList)
                {
                    ISheet sheet = workbook.GetSheet(sheetName);

                    for (int row = 1; row <= sheet.LastRowNum; row++)  // 假设第一行为表头
                    {
                        IRow currentRow = sheet.GetRow(row);

                        if (currentRow == null || currentRow.GetCell(2) == null || string.IsNullOrEmpty(currentRow.GetCell(2).ToString()))
                            continue;

                        var studentScheduleData = new StudentSchedule
                        {
                            Id = int.Parse(currentRow.GetCell(0).ToString()),   // 序号
                            Class = currentRow.GetCell(1).ToString(),           // 班级
                            StudentName = currentRow.GetCell(2).ToString(),     // 学生姓名
                            Courses = new Dictionary<string, string> {
                                {"周一", currentRow.GetCell(3).ToString()},
                                {"周二", currentRow.GetCell(4).ToString()},
                                {"周三", currentRow.GetCell(5).ToString()},
                                {"周四①", currentRow.GetCell(6).ToString()},
                                {"周四②", currentRow.GetCell(7).ToString()},
                            }                            
                        };

                        studentSchedules.Add(studentScheduleData);
                    }
                }
            }

            return studentSchedules;
        }

        public string ValidateCourse(List<Schedule> schedules, List<StudentSchedule> studentSchedules)
        {
            var schedulesUnique = schedules.Select(x => x.CourseFormat).Distinct().ToList();
            var studentSchedulesunique = studentSchedules.SelectMany(x => x.Courses.Where(x => x.Value != "不上")).Select(x => StudentSchedule.CourseFormat(x.Value)).Distinct().ToList();

            var result1 = schedulesUnique.Except(studentSchedulesunique);
            var result2 = studentSchedulesunique.Except(schedulesUnique);

            var validateResult = "";
            if (result1 != null && result1.Count() > 0)
            {
                validateResult += "课程表异常课程：" + string.Join(",", result1) + "\r\n";
            }
            if (result2 != null && result2.Count() > 0)
            {
                validateResult += "报名表异常课程：" + string.Join(",", result2) + "\r\n";
            }

            return validateResult;
        }

        public void GenerateAttendanceSheet(List<Schedule> schedules, List<StudentSchedule> studentSchedules, ExportType exportType, string saveFilePath)
        {
            // 创建新的工作簿
            IWorkbook workbook = new XSSFWorkbook();

            // 定义字体和样式
            var defaultFont = workbook.CreateFont();
            defaultFont.FontName = "微软雅黑";  // 设置字体为微软雅黑
            defaultFont.FontHeightInPoints = 11;

            var boldFont = workbook.CreateFont();
            boldFont.FontName = "微软雅黑";
            boldFont.FontHeightInPoints = 11;
            boldFont.IsBold = true;  // 加粗

            // 设置背景颜色
            var darkBlueColor = new XSSFColor(new byte[] { 20, 80, 184 }); // #1450B8 (RGB: 20, 80, 184)
            var blueColor = new XSSFColor([229, 239, 255]);  // RGB for #DDEBF7
            var customBorderColor = new XSSFColor(new byte[] { 153, 190, 255 }); // 定义自定义颜色 #99BEFF (RGB: 153, 190, 255)

            // 白色加粗字体
            XSSFFont whiteBoldFont = (XSSFFont)workbook.CreateFont();
            whiteBoldFont.IsBold = true;
            whiteBoldFont.Color = IndexedColors.White.Index;

            // 加粗且居中的样式
            var boldCenteredStyle = workbook.CreateCellStyle();
            boldCenteredStyle.Alignment = HorizontalAlignment.Center;
            boldCenteredStyle.VerticalAlignment = VerticalAlignment.Center;
            boldCenteredStyle.SetFont(boldFont);  // 设置加粗字体
            boldCenteredStyle.BorderBottom = BorderStyle.Thin;
            boldCenteredStyle.BorderTop = BorderStyle.Thin;
            boldCenteredStyle.BorderLeft = BorderStyle.Thin;
            boldCenteredStyle.BorderRight = BorderStyle.Thin;
            ((XSSFCellStyle)boldCenteredStyle).SetBottomBorderColor(customBorderColor);
            ((XSSFCellStyle)boldCenteredStyle).SetTopBorderColor(customBorderColor);
            ((XSSFCellStyle)boldCenteredStyle).SetLeftBorderColor(customBorderColor);
            ((XSSFCellStyle)boldCenteredStyle).SetRightBorderColor(customBorderColor);

            // 数据标题样式
            var seqHeaderStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            seqHeaderStyle.Alignment = HorizontalAlignment.Center;
            seqHeaderStyle.VerticalAlignment = VerticalAlignment.Center;
            seqHeaderStyle.FillPattern = FillPattern.SolidForeground;
            seqHeaderStyle.SetFont(whiteBoldFont);
            seqHeaderStyle.SetFillForegroundColor(darkBlueColor);
            seqHeaderStyle.BorderBottom = BorderStyle.Thin;
            seqHeaderStyle.BorderTop = BorderStyle.Thin;
            seqHeaderStyle.BorderLeft = BorderStyle.Thin;
            seqHeaderStyle.BorderRight = BorderStyle.Thin;
            ((XSSFCellStyle)seqHeaderStyle).SetBottomBorderColor(customBorderColor);
            ((XSSFCellStyle)seqHeaderStyle).SetTopBorderColor(customBorderColor);
            ((XSSFCellStyle)seqHeaderStyle).SetLeftBorderColor(customBorderColor);
            ((XSSFCellStyle)seqHeaderStyle).SetRightBorderColor(customBorderColor);

            // 无背景数据样式
            var centeredStyle = workbook.CreateCellStyle();
            centeredStyle.Alignment = HorizontalAlignment.Center;
            centeredStyle.VerticalAlignment = VerticalAlignment.Center;
            centeredStyle.SetFont(defaultFont);  // 设置默认字体
            centeredStyle.BorderBottom = BorderStyle.Thin;
            centeredStyle.BorderTop = BorderStyle.Thin;
            centeredStyle.BorderLeft = BorderStyle.Thin;
            centeredStyle.BorderRight = BorderStyle.Thin;
            ((XSSFCellStyle)centeredStyle).SetBottomBorderColor(customBorderColor);
            ((XSSFCellStyle)centeredStyle).SetTopBorderColor(customBorderColor);
            ((XSSFCellStyle)centeredStyle).SetLeftBorderColor(customBorderColor);
            ((XSSFCellStyle)centeredStyle).SetRightBorderColor(customBorderColor);

            // 浅蓝色背景数据样式
            var blueBackgroundStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            blueBackgroundStyle.Alignment = HorizontalAlignment.Center;
            blueBackgroundStyle.VerticalAlignment = VerticalAlignment.Center;
            blueBackgroundStyle.SetFont(defaultFont);
            blueBackgroundStyle.SetFillForegroundColor(blueColor); // 使用 IndexedColor
            blueBackgroundStyle.FillPattern = FillPattern.SolidForeground;
            blueBackgroundStyle.BorderBottom = BorderStyle.Thin;
            blueBackgroundStyle.BorderTop = BorderStyle.Thin;
            blueBackgroundStyle.BorderLeft = BorderStyle.Thin;
            blueBackgroundStyle.BorderRight = BorderStyle.Thin;
            ((XSSFCellStyle)blueBackgroundStyle).SetBottomBorderColor(customBorderColor);
            ((XSSFCellStyle)blueBackgroundStyle).SetTopBorderColor(customBorderColor);
            ((XSSFCellStyle)blueBackgroundStyle).SetLeftBorderColor(customBorderColor);
            ((XSSFCellStyle)blueBackgroundStyle).SetRightBorderColor(customBorderColor);


            // 加粗且居中的样式
            var boldStyle = workbook.CreateCellStyle();
            //boldStyle.Alignment = HorizontalAlignment.Center;
            boldStyle.VerticalAlignment = VerticalAlignment.Center;
            boldStyle.SetFont(boldFont);  // 设置加粗字体
            boldStyle.BorderBottom = BorderStyle.Thin;
            boldStyle.BorderTop = BorderStyle.Thin;
            boldStyle.BorderLeft = BorderStyle.Thin;
            boldStyle.BorderRight = BorderStyle.Thin;
            ((XSSFCellStyle)boldStyle).SetBottomBorderColor(customBorderColor);
            ((XSSFCellStyle)boldStyle).SetTopBorderColor(customBorderColor);
            ((XSSFCellStyle)boldStyle).SetLeftBorderColor(customBorderColor);
            ((XSSFCellStyle)boldStyle).SetRightBorderColor(customBorderColor);

            IEnumerable<IGrouping<string, Schedule>> scheduleGroups = null;

            switch (exportType)
            {
                case ExportType.BySchedule:
                    scheduleGroups = schedules.GroupBy(c => c.Course).OrderBy(x => x.Key);
                    break;
                case ExportType.ByTeacher:
                    scheduleGroups = schedules.GroupBy(c => c.Teacher).OrderBy(x => x.Key);
                    break;
            }

            // 按课程分组并生成签到表
            foreach (var scheduleGroup in scheduleGroups)
            {
                // 创建新的Sheet页
                ISheet sheet = workbook.CreateSheet(scheduleGroup.Key.Replace("/", "_"));

                // 设置列宽
                sheet.SetColumnWidth(0, 10 * 256); // 设置日期列宽
                sheet.SetColumnWidth(1, 20 * 256); // 设置上课地点列宽
                sheet.SetColumnWidth(2, 25 * 256); // 设置课程列宽
                sheet.SetColumnWidth(3, 10 * 256); // 设置出勤列宽

                // 添加签到表标题
                var headers = new List<string> { "日期", "上课地点", "课程", "出勤：✓" };
                IRow headerRow = sheet.CreateRow(0);
                for (int i = 0; i < headers.Count; i++)
                {
                    var cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headers[i]);
                    cell.CellStyle = boldCenteredStyle;  // 标题行加粗并居中
                }

                // 添加课程数据和学生数据
                int rowIndex = 1;
                foreach (var schedule in scheduleGroup)
                {
                    // 填写课程信息
                    IRow courseRow = sheet.CreateRow(rowIndex++);
                    courseRow.CreateCell(0).SetCellValue(schedule.Date);
                    courseRow.CreateCell(1).SetCellValue(schedule.Location);
                    courseRow.CreateCell(2).SetCellValue(schedule.Course);
                    courseRow.CreateCell(3).SetCellValue("缺席：X");
                    foreach (ICell cell in courseRow.Cells)
                    {
                        cell.CellStyle = boldCenteredStyle;  // 设置居中样式
                    }

                    // 添加学生表头
                    IRow studentHeaderRow = sheet.CreateRow(rowIndex++);
                    var studentHeaders = new List<string> { "序号", "班级", "学生姓名", "出勤" };
                    for (int i = 0; i < studentHeaders.Count; i++)
                    {
                        var cell = studentHeaderRow.CreateCell(i);
                        cell.SetCellValue(studentHeaders[i]);
                        cell.CellStyle = seqHeaderStyle;  // 学生表头加粗并居中
                    }

                    // 填写学生数据
                    int seq = 1;
                    foreach (var studentSchedule in studentSchedules)
                    {
                        if (studentSchedule.MatchesCourse(schedule))
                        {
                            IRow studentRow = sheet.CreateRow(rowIndex++);

                            // 判断是否为偶数行并应用浅蓝色背景
                            ICellStyle rowStyle = (seq % 2 == 0) ? blueBackgroundStyle : centeredStyle;

                            studentRow.CreateCell(0).SetCellValue(seq++);
                            studentRow.CreateCell(1).SetCellValue(studentSchedule.Class);
                            studentRow.CreateCell(2).SetCellValue(studentSchedule.StudentName);
                            studentRow.CreateCell(3).SetCellValue("");
                            foreach (ICell cell in studentRow.Cells)
                            {
                                cell.CellStyle = rowStyle;  // 学生数据居中
                            }
                        }
                    }

                    // 添加应到和实到的行
                    IRow attendanceRow = sheet.CreateRow(rowIndex++);
                    attendanceRow.CreateCell(0).SetCellValue("应到                         实到");
                    attendanceRow.CreateCell(1);
                    attendanceRow.CreateCell(2);
                    attendanceRow.CreateCell(3);
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, 3));  // 合并应到、实到单元格
                    foreach (ICell cell in attendanceRow.Cells)
                    {
                        cell.CellStyle = boldStyle;  // 加粗
                    }

                    IRow teacherRow = sheet.CreateRow(rowIndex++);
                    teacherRow.CreateCell(0).SetCellValue($"老师：{schedule.Teacher}            签名：");
                    teacherRow.CreateCell(1);
                    teacherRow.CreateCell(2);
                    teacherRow.CreateCell(3);
                    foreach (ICell cell in teacherRow.Cells)
                    {
                        cell.CellStyle = boldStyle;  // 加粗
                    }
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, 3));  // 合并老师和签名

                    IRow remarkRow = sheet.CreateRow(rowIndex++);
                    remarkRow.CreateCell(0).SetCellValue("备注：");
                    remarkRow.CreateCell(1);
                    remarkRow.CreateCell(2);
                    remarkRow.CreateCell(3);
                    foreach (ICell cell in remarkRow.Cells)
                    {
                        cell.CellStyle = boldStyle;  // 加粗
                    }
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, 3));  // 合并应到、实到单元格

                    IRow dateRow = sheet.CreateRow(rowIndex++);
                    dateRow.CreateCell(0).SetCellValue("日期：_______年_______月_________日");
                    dateRow.CreateCell(1);
                    dateRow.CreateCell(2);
                    dateRow.CreateCell(3);
                    foreach (ICell cell in dateRow.Cells)
                    {
                        cell.CellStyle = boldStyle;  // 加粗
                    }
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, 3));  // 合并应到、实到单元格

                    // 为下一个签到表留出空行
                    rowIndex += 2;
                }
            }

            // 保存Excel文件
            using (var fs = new FileStream(saveFilePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }
    }
}
